package com.zizo.autohome.word.flowtrend.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.zizo.util.ConnectionPool;

public class BizIODao {
	private final static Connection conn198 = ConnectionPool.getConnection("Projects_2016", "localhost");
	private final static Connection conn168 = ConnectionPool.getConnection("carvoice_biz", "192.169.0.168");
	private String tablename;

	public void alterTable() throws Exception {
		Statement st = conn168.createStatement();
		String sql1 = "DROP TABLE IF EXISTS biz_io_temp";
		String sql2 = "create table biz_io_temp like biz_io";
		st.executeUpdate(sql1);
		st.executeUpdate(sql2);
		get();
		System.out.println("Get() Finish");
		rename();
		System.out.println("Rename() Finish");
		updateSlcnt();
		System.out.println("updateSlcnt() Finish");
		updateOutInCnt();
		System.out.println("GG~~");
	}

	public void get() throws Exception {
		Statement st = conn198.createStatement();
		String sql = "SELECT io_inid,io_outid,io_year,io_month,io_pro,io_city,COUNT(*) AS io_cnt FROM (SELECT flowtrend_cid AS io_inid,flowtrend_carid AS io_outid,YEAR(flowtrend_time) AS io_year,MONTH(flowtrend_time) AS io_month,NULL AS io_pro,flowtrend_province AS io_city FROM all_in_out_kb_"
				+ tablename
				+ " WHERE flowtrend_carid !=0 AND flowtrend_carid!=flowtrend_cid UNION ALL SELECT flowtrend_cid AS io_inid,flowtrend_carid AS io_outid,YEAR(flowtrend_time) AS io_year,MONTH(flowtrend_time) AS io_month,flowtrend_province AS io_pro,flowtrend_area AS io_city FROM all_in_out_bbs_"
				+ tablename
				+ " WHERE flowtrend_carid !=0 AND flowtrend_carid!=flowtrend_cid) AS b where io_year>2009 GROUP BY io_inid,io_outid,io_year,io_month,io_pro,io_city;";
		ResultSet rs = st.executeQuery(sql);
		while (rs.next()) {
			int inid = rs.getInt(1);
			int outid = rs.getInt(2);
			int year = rs.getInt(3);
			int month = rs.getInt(4);
			String pro = rs.getString(5);
			if (pro == null) {
				pro = "";
			}
			String city = rs.getString(6);
			int cnt = rs.getInt(7);
			insert(inid, outid, year, month, pro, city, cnt);
		}
	}

	public void insert(int inid, int outid, int year, int month, String pro, String city, int cnt) throws Exception {
		String sql = "INSERT INTO biz_io_temp(io_inid,io_outid,io_year,io_month,io_pro,io_city,io_iocnt) values(?,?,?,?,?,?,?)";
		PreparedStatement ps = conn168.prepareStatement(sql);
		ps.setInt(1, inid);
		ps.setInt(2, outid);
		ps.setInt(3, year);
		ps.setInt(4, month);
		ps.setString(5, pro);
		ps.setString(6, city);
		ps.setInt(7, cnt);
		ps.executeUpdate();
	}

	public void rename() throws Exception {
		Statement st = conn168.createStatement();
		String sql1 = "DROP TABLE IF EXISTS biz_io";
		String sql2 = "alter table biz_io_temp rename biz_io";
		st.executeUpdate(sql1);
		st.executeUpdate(sql2);
	}

	public void updateSlcnt() throws Exception {
		String s1 = "{call SlrankUpdate()}";
		CallableStatement st = null;
		try {
			st = conn168.prepareCall(s1);
			st.executeUpdate(s1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * @Title: updateOutInCnt
	 * @Description: TODO 更新OUT&IN cnt
	 * @return void
	 * @author Alexis
	 */
	public void updateOutInCnt() {
		CallableStatement proc = null;
		try {
			proc = conn168.prepareCall("{CALL oicntAoutslcnt()}");
			proc.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				proc.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public BizIODao(String tablename) {
		super();
		this.tablename = tablename;
	}

	public static void main(String[] args) throws Exception {
		BizIODao dao = new BizIODao("0112");
		dao.alterTable();
	}
}
